#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from lib.load_data import *
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

#mysql
def set_order_conn():    
    conn = HandleMysql('emclottery.db.58dns.org','58885','emclottery_rw','P3%J&ePihR','dbwww58com_emclottery')
    conn.connect()
    return conn

#mysql - stats
def set_stats_conn():    
    conn = HandleMysql('10.5.20.16','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_shop_stats')
    conn.connect()
    return conn

def get_wan_order_data(date):
    stat_conn = set_stats_conn()
    conn = set_order_conn()
    stat_table = "wan_order_info"
    line_list = []
    #game_id = 1
    game_dict = {'1':'14947110132481','4':'14947088374529','5':'14947129052673'}
    query = "select order_id,buyer_id,shop_id,order_title,pay_amount,pay_state,refund_product_count,create_time,order_title,pay_time from order_info where order_refer=7 and date(create_time)='%s'"%date
    #print query
    items = stat_conn.get_results(query)
    #print len(items)
    for item in items:
        order_id = item[0]
        user_id = item[1]
        shop_id = item[2]
        order_title = item[3]
        pay_amount = item[4]
        pay_state = item[5]
        refund_product_count = item[6]
        create_time = item[7]
        order_title = item[8]
        pay_time = item[9]
        if pay_time==None:
            pay_time = '0000-00-00'
            
        refund_state = 0
        if refund_product_count > 0:
            refund_state = 1
        
        query_tmp = "select company_id,company_name from wan_company where shop_id=%s"%shop_id
        #print query_tmp
        items_tmp = stat_conn.get_results(query_tmp)
        if len(items_tmp) == 0:
            continue
        
        company_id =items_tmp[0][0]
        company_name =items_tmp[0][1]
        
        server_id = 0
        query_tmp = "select gid,sid from t_gameorder where orderid=%s"%order_id
        #print query_tmp
        items_tmp = conn.get_results(query_tmp)
        if len(items_tmp) == 0:
            continue
        
        game_id = items_tmp[0][0]
        server_id = items_tmp[0][1]
        
        if game_dict.has_key("%s"%game_id):
            game_id = game_dict["%s"%game_id]
        
        game_name = ""
        query_tmp = "select game_name from wan_game where game_id=%s"%game_id
        #print query_tmp
        items_tmp = stat_conn.get_results(query_tmp)
        if len(items_tmp) > 0:
            game_name = items_tmp[0][0]
        
        param = [order_id,user_id,company_id,company_name,game_id,game_name,server_id,pay_amount,pay_state,refund_state,create_time,order_title,pay_time]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "./shop/order_data/"
    file_name = "wan_order_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    q = "delete from wan_order_info where date(create_time)='%s'"%date
    stat_conn.execute_sql(q)

    load_data(stat_conn,path+file_name,stat_table,'\t')    
    conn.close()
    stat_conn.close()

def get_order_day(date):
    stat_conn = set_stats_conn()
    conn = set_order_conn()
    stat_table = "wan_order_day"
    line_list = []
    
    query = "select company_id,company_name,game_id,game_name,server_id,count(case when date(create_time)='%s' then 1 end),count(distinct case when pay_state=1 then user_id end),count(case when pay_state=1 then 1 end),ifnull(sum(case when pay_state=1 then pay_amount end),0),count(case when refund_state=1 then 1 end),ifnull(sum(case when refund_state=1 then pay_amount end),0),server_name from wan_order_info where  date(create_time)='%s' group by company_id,game_id,server_id;"%(date,date)
    #print query
    items = stat_conn.get_results(query)
    #print len(items)
    for item in items:
        company_id = item[0]
        company_name = item[1]
        game_id = item[2]
        game_name = item[3]
        server_id = item[4]
        
        order_count = item[5]
        pay_user_count = item[6]
        pay_order_count = item[7]
        pay_amount = item[8]
        refund_order_count = item[9]
        refund_amount = item[10]
        server_name = item[11]
        
        company_rate = 0.0
        team_rate =0.0
        profit_rate = 0.0
        query_tmp = "select company_rate,team_rate,profit_rate from wan_game_profile where game_id=%s;"%game_id
        items_tmp = stat_conn.get_results(query_tmp)
        if len(items_tmp) > 0:
            company_rate = items_tmp[0][0]
            team_rate = items_tmp[0][1]
            profit_rate = items_tmp[0][2]
        
        income_amount = pay_amount - refund_amount
        com_commission_amount = income_amount*company_rate
        team_commission_amount = income_amount*team_rate        
        profit_amount = income_amount * profit_rate
        
        arpu = 0
        if pay_amount > 0:
            arpu = pay_amount/pay_user_count
        
        param = [date,company_id,company_name,game_id,game_name,server_id,order_count,pay_user_count,pay_order_count,pay_amount,refund_order_count,refund_amount,income_amount,profit_amount,com_commission_amount,team_commission_amount,arpu,server_name]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "./shop/order_data/"
    file_name = "wan_order_stat_day_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    query = "delete from wan_order_day where stat_date='%s'"%date
    stat_conn.execute_sql(query)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    conn.close()
    stat_conn.close()

def get_order_hour(date):
    stat_conn = set_stats_conn()
    conn = set_order_conn()
    stat_table = "wan_order_hour"
    line_list = []
    
    query = "select hour(create_time),company_id,company_name,game_id,game_name,server_id,count(case when date(create_time)='%s' then 1 end),count(distinct case when pay_state=1 then user_id end),count(case when pay_state=1 then 1 end),ifnull(sum(case when pay_state=1 then pay_amount end),0),count(case when refund_state=1 then 1 end),ifnull(sum(case when refund_state=1 then pay_amount end),0),server_name from wan_order_info where date(create_time)='%s' group by hour(create_time),company_id,game_id,server_id;"%(date,date)
    #print query
    items = stat_conn.get_results(query)
    #print len(items)
    for item in items:
        hour = item[0]
        company_id = item[1]
        company_name = item[2]
        game_id = item[3]
        game_name = item[4]
        server_id = item[5]
        
        order_count = item[6]
        pay_user_count = item[7]
        pay_order_count = item[8]
        pay_amount = item[9]
        refund_order_count = item[10]
        refund_amount = item[11]
        server_name = item[12]
        
        company_rate = 0.0
        team_rate =0.0
        profit_rate = 0.0
        query_tmp = "select company_rate,team_rate,profit_rate from wan_game_profile where game_id=%s;"%game_id
        items_tmp = stat_conn.get_results(query_tmp)
        if len(items_tmp) > 0:
            company_rate = items_tmp[0][0]
            team_rate = items_tmp[0][1]
            profit_rate = items_tmp[0][2]
        
        income_amount = pay_amount - refund_amount
        com_commission_amount = income_amount*company_rate
        team_commission_amount = income_amount*team_rate        
        profit_amount = income_amount * profit_rate
        
        arpu = 0
        if pay_amount > 0:
            arpu = pay_amount/pay_user_count
        
        param = [date,hour,company_id,company_name,game_id,game_name,server_id,order_count,pay_user_count,pay_order_count,pay_amount,refund_order_count,refund_amount,income_amount,profit_amount,com_commission_amount,team_commission_amount,arpu,server_name]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "./shop/order_data/"
    file_name = "wan_order_stat_day_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    query = "delete from wan_order_hour where stat_date='%s'"%date
    stat_conn.execute_sql(query)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    conn.close()
    stat_conn.close()
    
def get_server_list():
    stat_conn = set_stats_conn()
    
    query = "truncate table wan_server;"
    stat_conn.execute_sql(query)
    
    query = " insert into wan_server(server_id,game_id,server_name) select server_id,game_id,server_name from wan_order_info  where date(create_time)>'2013-05-09' group by server_id,server_name,game_id"
    stat_conn.execute_sql(query)
    
    stat_conn.close()
    
def get_channel_day( date ):
    stat_conn = set_stats_conn()
    stat_table = "wan_channel_day"
    line_list = []
    
    query = "select channel_id,channel_name,channel_utm from wan_channel where is_all=1"
    ch_list_full =  stat_conn.get_results(query)
    query = "select channel_id,channel_name,channel_utm from wan_channel where is_all=0"
    ch_list_mat =  stat_conn.get_results(query)
    
    query = "select server_name,utm_source,pv,uv,visits,new_uv from wan_flux_day where stat_date='%s'"%date
    items = stat_conn.get_results(query)
    for item in items:
        server_name = item[0]
        utm = item[1]
        pv = item[2]
        apa = item[3]
        login_count = item[4]
        reg_count = item[5]
        
        server_id = 0
        game_id = 0
        game_name = ''
        company_id = 0
        company_name = ''
        
        q = "select server_id,game_id from wan_server where server_name='%s'"%server_name
        itms = stat_conn.get_results(q)
        if len(itms) > 0:
            server_id = itms[0][0]
            game_id = itms[0][1]
        
        if game_id > 0:
            q = "select company_id,game_name from wan_game where game_id=%s"%game_id
            itms = stat_conn.get_results(q)
            if len(itms) > 0:
                company_id = itms[0][0]
                game_name = itms[0][1]
        if company_id > 0:
            q = "select company_name from wan_company where company_id=%s"%company_id
            itms = stat_conn.get_results(q)
            if len(itms) > 0:
                company_name = itms[0][0]
        
        channel_id = 0
        channel_name = ''
        for ch in ch_list_full:
            ch_utm = ch[2]
            if utm == ch_utm:
                channel_id = ch[0]
                channel_name = ch[1]
                break
            
        if channel_id == 0:
            for ch in ch_list_mat:
                ch_utm = ch[2]
                #print ch_utm,utm
                if utm.startswith(ch_utm):
                    channel_id = ch[0]
                    channel_name = ch[1]
                    break
        pay_user_count = 0
        pay_amount = 0
        arpu = 0
        #if channel_id == 0 :
        #    print utm
        param = [date,channel_id,channel_name,company_id,company_name,game_id,game_name,server_id,server_name,pv,reg_count,login_count,pay_user_count,apa,arpu,pay_amount]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    path = "./shop/order_data/"
    file_name = "wan_channel_stat_day_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    q = "delete from wan_channel_day where date(stat_date)='%s'"%date
    stat_conn.execute_sql(q)
    
    load_data(stat_conn,path+file_name,stat_table,'\t')    
    stat_conn.close()
        
        
#logic
def execute(date):
   #get_channel_day( date )
    try:
        pass
        get_wan_order_data( date )
        get_server_list()
    except Exception,e:
        print 'get_wan_order_data error:',e
    try:
        pass
        get_order_day( date )
    except Exception,e:
        print 'get_order_day error:',e
    try:
        pass
        get_order_hour( date )
    except Exception,e:
        print 'get_order_hour error:',e
    
    try:
        pass
        get_channel_day( date )
    except Exception,e:
        print 'get_channel_day error:',e
#
def main():

   #date = '2012-05-28'
   #execute(date)
   inp = 0
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        inp = sys.argv[1]
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
    
   if inp == "1":
        today = get_today()
        get_wan_order_data( today )
        get_server_list()
        get_order_hour(today )
   else:
    stat_date=start_date
    while stat_date>=start_date and stat_date<=end_date:
         print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
         #
         execute( stat_date) 
         print "The program of the date of %s is end at %s " %(stat_date,get_now())
         stat_date=var_date(stat_date)


if __name__=='__main__':
    main()
